package cn.topevery.garden.dal.dao.impl;

import cn.topevery.garden.dal.dao.ScheduleRecordDao;
import cn.topevery.garden.entity.po.Extent;
import cn.topevery.garden.entity.po.ScheduleRecord;
import cn.topevery.garden.entity.query.ScheduleRecordQuery;
import cn.topevery.garden.entity.vo.FacilitiesPageVO;
import cn.topevery.garden.entity.vo.ScheduleRecordVO;
import cn.topevery.garden.framework.base.BaseDaoImpl;
import hw.topevery.framework.db.CommandType;
import hw.topevery.framework.db.entity.DataPager;
import hw.topevery.framework.db.entity.SqlQueryMate;
import hw.topevery.framework.db.enums.ScriptConditionEnum;
import hw.topevery.framework.db.enums.ScriptExistsConditionEnum;
import hw.topevery.framework.entity.DbCsp;
import hw.topevery.framework.entity.KeyValue;
import org.springframework.stereotype.Repository;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * 排班记录表
 *
 * @author code-generate
 * @date 2021-12-28
 */
@Repository
public class ScheduleRecordDaoImpl extends BaseDaoImpl<ScheduleRecord> implements ScheduleRecordDao {
    @Override
    public <P extends DbCsp> List<ScheduleRecord> getSearch(P para) {
        return null;
    }

    

    @Override
    public DataPager<ScheduleRecordVO> listPage(ScheduleRecordQuery query) {
        String table = "t_schedule_record tsr";

        String columns = "tsr.*";

        int pageIndex = query.pageIndex - 1;
        int pageSize = query.pageSize;

        SqlQueryMate sqlQueryMate = new SqlQueryMate(table).setColumns(columns).setOrderBy("tsr.c_id desc");
        sqlQueryMate
                .where(true, "tsr.c_db_status", ScriptConditionEnum.Eq, 0)
        ;

        return getDataPager(sqlQueryMate, pageSize, pageIndex, ScheduleRecordVO.class);
    }

    @Override
    public ScheduleRecord getEntityByColumn(String key,String value){
        KeyValue<String> map=new KeyValue<>();
        map.key=key;
        map.value=value;
        return getEntityByColumnEqValue(map);
    }

    @Override
    public void createScheduleRecord() {
        String sql="insert into t_schedule_record(c_date,c_obj_id,c_obj_type,c_obj_name,c_extent_id,c_extent_name,c_shift_id,c_task_id,c_task_name,c_task_content,c_db_create_id,c_db_create_date,c_db_last_update_date,c_db_last_update_id,c_db_status)\n" +
                "select*From (\n" +
                "select cast(now() as date) c_date,tp.c_id c_obj_id,1 c_obj_type,tp.c_name c_obj_name,tp.c_extent_id,te.c_name c_extent_name,tp.c_shift_id,ttpr.c_task_id,tti.c_name c_task_name,tti.c_remark c_task_content,1 c_db_create_id,now() c_db_create_date,now() c_db_last_update_date,1 c_db_last_update_id,0 c_db_status From t_person tp \n" +
                "join t_extent te on tp.c_extent_id=te.c_id\n" +
                "join t_dingtalk_duty tdu on tp.c_shift_id=tdu.c_id\n" +
                "left join t_task_person_relation ttpr on ttpr.c_person_id=tp.c_id\n" +
                "left join t_task_info tti on tti.c_id=ttpr.c_task_id\n" +
                "where tp.c_db_status=0\n" +
                "union\n" +
                "select cast(now() as date) c_date,tc.c_id c_obj_id,2 c_obj_type,tc.c_car_num c_obj_name,tc.c_extent_id,te.c_name c_extent_name,tc.c_shift_id,tc.c_task_id,tti.c_name c_task_name,tti.c_remark c_task_content,1 c_db_create_id,now() c_db_create_date,now() c_db_last_update_date,1 c_db_last_update_id,0 c_db_status From t_car tc \n" +
                "join t_extent te on tc.c_extent_id=te.c_id\n" +
                "join t_dingtalk_duty tdu on tc.c_shift_id=tdu.c_id\n" +
                "left join t_task_info tti on tti.c_id=tc.c_task_id\n" +
                "where tc.c_db_status=0\n" +
                ") t where not exists(\n" +
                "\tselect 1 from t_schedule_record tsr where tsr.c_date=t.c_date \n" +
                "and tsr.c_obj_id=t.c_obj_id and tsr.c_obj_type=t.c_obj_type)";
        run(sql,CommandType.Text,dbCommand -> {
            dbCommand.executeNonQuery();
        });
    }

    @Override
    public List<ScheduleRecordVO> statisticsData(ScheduleRecordQuery query) {

        List<KeyValue<?>> parameters=new ArrayList<>();
        String where ="";
        if (query.getName() != null && query.getName() != "") {
            where += "  and c_obj_name like :p_name";
            parameters.add(new KeyValue<String>("p_name","%"+query.getName()+"%"));
        }
        if (query.getExtentId() != null &&  query.getExtentId() > 0){
            where += "  and c_shift_id = :p_shift_id";
            parameters.add(new KeyValue<Integer>("p_shift_id",query.getExtentId()));
        }
        //mysql 语法
        String sql=  "select tsr.c_id,te.c_extent_id,te.c_extent_name,te.c_person_count,te.c_car_count,tdd.c_id c_shift_id,tdd.c_name c_shift_name,tsr.c_obj_id,tsr.c_obj_type,tsr.c_obj_name,tsr.c_task_id,tsr.c_task_name,tsr.c_task_content from (select c_extent_id,c_extent_name,ifnull(sum((case when c_obj_type=1 then 1 else 0 end)),0) c_person_count,ifnull(sum((case when c_obj_type=2 then 1 else 0 end)),0) c_car_count From t_schedule_record where c_date=:p_date "
                +where+" group by c_extent_id,c_extent_name) te join t_schedule_record tsr on te.c_extent_id=tsr.c_extent_id join (select tdd.c_id,CONCAT(tdd.c_name,' (',GROUP_CONCAT(CONCAT(tddt.c_begin,'-',tddt.c_end)),')') c_name From t_dingtalk_duty tdd join t_dingtalk_duty_times tddt on tdd.c_data_id=tddt.c_duty_data_id group by tdd.c_id ) tdd on tsr.c_shift_id=tdd.c_id where tsr.c_date=:p_date "
                +where+" order by c_extent_id,c_shift_id";


//        String sql=  "select tsr.c_id,te.c_extent_id,te.c_extent_name,te.c_person_count,te.c_car_count,tdd.c_id c_shift_id,tdd.c_name c_shift_name,tsr.c_obj_id,tsr.c_obj_type,tsr.c_obj_name,tsr.c_task_id,tsr.c_task_name,tsr.c_task_content from (select c_extent_id,c_extent_name,ifnull(sum((case when c_obj_type=1 then 1 else 0 end)),0) c_person_count,ifnull(sum((case when c_obj_type=2 then 1 else 0 end)),0) c_car_count From t_schedule_record where c_date=:p_date "
//                +where+" group by c_extent_id,c_extent_name) te join t_schedule_record tsr on te.c_extent_id=tsr.c_extent_id join (select tdd.c_id," +
//                "CONCAT(tdd.c_name,' (',listagg(CONCAT(tddt.c_begin,'-',tddt.c_end)) within group (order by tdd.c_id),')') c_name " +
//                "From t_dingtalk_duty tdd join t_dingtalk_duty_times tddt on tdd.c_data_id=tddt.c_duty_data_id group by tdd.c_id,tdd.c_name  ) tdd on tsr.c_shift_id=tdd.c_id where tsr.c_date=:p_date "
//                +where+" order by c_extent_id,c_shift_id";

        parameters.add(new KeyValue<String>("p_date",query.getDate()));

        return getList(sql,CommandType.Text,parameters,ScheduleRecordVO.class);
    }

    @Override
    public Map getExtentPeopleWorkInfo(ScheduleRecordQuery query) {
        String table = "t_schedule_record tsr\n" +
                "join t_person tp on tsr.c_obj_id=tp.c_id\n" +
                "join t_curr_status_people tcsp on  tp.c_user_id=tcsp.c_user_id  and tsr.c_date=cast(now() as date)";

        String columns = "count(1) count /*排班数*/\t\n" +
                "\t,sum(case when tcsp.c_is_in_work_time=1 then 1 else 0 end) shouldCount\n" +
                "\t,sum(case when tcsp.c_is_in_extent=1 and tcsp.c_is_in_work_time=1 then 1 else 0 end) onWorkCount /*在岗数*/\n" +
                "\t,sum(case when tcsp.c_is_in_work_time=1 and tcsp.c_is_in_extent=0 then 1 else 0 end) onErrorCount /*异常数*/\n";

        SqlQueryMate sqlQueryMate = new SqlQueryMate(table).setColumns(columns);
        sqlQueryMate
                .where(true,"tsr.c_obj_type",ScriptConditionEnum.Eq,1)
                .where(query.getExtentId()!=null &&query.getExtentId()>0, "tsr.c_extent_id", ScriptConditionEnum.Eq, query.getExtentId())
        ;
        return getOneMap(sqlQueryMate);
    }

    @Override
    public Map getExtentCarWorkInfo(ScheduleRecordQuery query) {
        String table = "t_schedule_record tsr\n" +
                "join t_car tc on tsr.c_obj_id=tc.c_id\n" +
                "join t_curr_status_car tcsc on  tc.c_car_id=tcsc.c_car_id  and tsr.c_date=cast(now() as date)";

        String columns = "count(1) `count` /*排班数*/\t\n" +
                "\t,sum(case when tcsc.c_is_in_work_time=1 then 1 else 0 end) shouldCount\n" +
                "\t,sum(case when tcsc.c_is_in_extent=1 and tcsc.c_is_in_work_time=1 then 1 else 0 end) onWorkCount /*在岗数*/\n" +
                "\t,sum(case when tcsc.c_is_in_work_time=1 and tcsc.c_is_in_extent=0 then 1 else 0 end) onErrorCount /*异常数*/\n";

        SqlQueryMate sqlQueryMate = new SqlQueryMate(table).setColumns(columns);
        sqlQueryMate
                .where(true,"tsr.c_obj_type",ScriptConditionEnum.Eq,2)
                .where(query.getExtentId()!=null &&query.getExtentId()>0, "tsr.c_extent_id", ScriptConditionEnum.Eq, query.getExtentId())
        ;
        return getOneMap(sqlQueryMate);
    }

    @Override
    public List<Map<String, Object>> getExtentWorkCarList(ScheduleRecordQuery query) {
        String table = "t_schedule_record tsr\n" +
                "JOIN t_extent te ON te.c_id = tsr.c_extent_id\n" +
                "JOIN t_car tc ON tsr.c_obj_id = tc.c_id\n" +
                "JOIN t_curr_status_car tcsc ON tc.c_car_id = tcsc.c_car_id and tsr.c_date=cast(now() as date)";

        String columns = "tc.c_id id\n" +
                "\t,tc.c_car_id objId\n" +
                "\t,tc.c_car_num name\n" +
                "\t,te.c_name extentName\n" +
                "\t,tc.c_type_name typeName\n" +
                "\t,tcsc.c_online_type onlineType\n" +
                "\t,c_onguard_type onguardType\n" +
                "\t,tcsc.c_is_in_extent isInExtent\n" +
                "\t,tcsc.c_is_in_work_time isInWorkTime\n" +
                "\t,(select c_file_id from t_car_file tcf where tcf.c_file_type_id=2 and tcf.c_main_id=tc.c_id limit 1) imageUrl\n" +
                "\t,(\n" +
                "\t\tSELECT sum(TIMESTAMPDIFF(SECOND, CONCAT (\n" +
                "\t\t\t\t\t\tcast(now() AS DATE)\n" +
                "\t\t\t\t\t\t,' '\n" +
                "\t\t\t\t\t\t,tddt.c_begin\n" +
                "\t\t\t\t\t\t), CONCAT (\n" +
                "\t\t\t\t\t\tcast(now() AS DATE)\n" +
                "\t\t\t\t\t\t,' '\n" +
                "\t\t\t\t\t\t,tddt.c_end\n" +
                "\t\t\t\t\t\t)))\n" +
                "\t\tFROM t_dingtalk_duty_times tddt\n" +
                "\t\tJOIN t_dingtalk_duty tdd ON tdd.c_data_id = tddt.c_duty_data_id\n" +
                "\t\tWHERE tdd.c_id = tsr.c_shift_id\n" +
                "\t\t) totalTime\n" +
                "\t,tcsc.c_online_time onlineTime\n" +
                "\t,tcsc.c_valid_time validTime\n" +
                "\t,tcsc.c_mileage mileage\n" +
                "\t,tcsc.c_geo_x lng\n" +
                "\t,tcsc.c_geo_y lat\n" +
                "\t,tcsc.c_gps_date gpsDate";

        SqlQueryMate sqlQueryMate = new SqlQueryMate(table).setColumns(columns);
        sqlQueryMate
                .where(true,"tsr.c_obj_type",ScriptConditionEnum.Eq,2)
                .where(query.getExtentId()!=null&&query.getExtentId()>0, "tsr.c_extent_id", ScriptConditionEnum.Eq, query.getExtentId())
                .where(query.getIsInExtent()!=null, "tcsc.c_is_in_extent", ScriptConditionEnum.Eq, query.getIsInExtent())
                .where(query.getIsInWorkTime()!=null, "tcsc.c_is_in_work_time", ScriptConditionEnum.Eq, query.getIsInWorkTime())
        ;
        return getMap(sqlQueryMate);
    }

    @Override
    public List<Extent> getObjWorkExtent(ScheduleRecordQuery query) {
        String table = "t_schedule_record tsr join\n" +
                "t_extent te on tsr.c_extent_id=te.c_id";

        String columns = "distinct te.*";

        SqlQueryMate sqlQueryMate = new SqlQueryMate(table).setColumns(columns);
        sqlQueryMate
                .where(true,"tsr.c_obj_type",ScriptConditionEnum.Eq,query.getObjectTypeId())
                .whereExists(query.getObjectTypeId().equals(1), ScriptExistsConditionEnum.EXISTS,
                        "select 1 from t_person tp where tp.c_id=tsr.c_obj_id and tp.c_user_id=:objId",
                        new KeyValue[]{ new KeyValue("objId",query.dataId) }
                        )
                .whereExists(query.getObjectTypeId().equals(2), ScriptExistsConditionEnum.EXISTS,
                        "select 1 from t_car tc where tc.c_id=tsr.c_obj_id and tc.c_car_id=:objId",
                        new KeyValue[]{ new KeyValue("objId",query.dataId) }
                ).whereExists(true,ScriptExistsConditionEnum.EXISTS,
                "select 1 from dual where tsr.c_date between cast(:beginDate as date) and cast(:endDate as date)",
                new KeyValue[]{
                        new KeyValue("beginDate",query.beginDate),
                        new KeyValue("endDate",query.endDate),
                })
        ;
        return selectList(sqlQueryMate,Extent.class);
    }

    @Override
    public List<FacilitiesPageVO> getObjWorkTaskFacilities(ScheduleRecordQuery query) {
        String table = "t_schedule_record tsr \n" +
                "join t_task_facilities_relation ttfr on tsr.c_task_id=ttfr.c_task_id\n" +
                "join t_facilities tf on ttfr.c_facilities_id=tf.c_id";

        String columns = "distinct tf.*";

        SqlQueryMate sqlQueryMate = new SqlQueryMate(table).setColumns(columns);
        sqlQueryMate
                .where(true,"tsr.c_obj_type",ScriptConditionEnum.Eq,query.getObjectTypeId())
                .whereExists(query.getObjectTypeId().equals(1), ScriptExistsConditionEnum.EXISTS,
                        "select 1 from t_person tp where tp.c_id=tsr.c_obj_id and tp.c_user_id=:objId",
                        new KeyValue[]{ new KeyValue("objId",query.dataId) }
                )
                .whereExists(query.getObjectTypeId().equals(2), ScriptExistsConditionEnum.EXISTS,
                        "select 1 from t_car tc where tc.c_id=tsr.c_obj_id and tc.c_car_id=:objId",
                        new KeyValue[]{ new KeyValue("objId",query.dataId) }
                ).whereExists(true,ScriptExistsConditionEnum.EXISTS,
                "select 1 from dual where tsr.c_date between cast(:beginDate as date) and cast(:endDate as date)",
                new KeyValue[]{
                        new KeyValue("beginDate",query.beginDate),
                        new KeyValue("endDate",query.endDate),
                })
        ;
        return selectList(sqlQueryMate,FacilitiesPageVO.class);
    }

    @Override
    public List<Map<String,Object>> getExtentWorkPeopleList(ScheduleRecordQuery query) {
        String table = "t_schedule_record tsr\n" +
                "JOIN t_extent te ON te.c_id = tsr.c_extent_id\n" +
                "JOIN t_person tp ON tsr.c_obj_id = tp.c_id\n" +
                "JOIN t_sys_user tsu ON tsu.c_user_id = tp.c_user_id\n" +
                "JOIN t_curr_status_people tcsp ON tp.c_user_id = tcsp.c_user_id and tsr.c_date=cast(now() as date)";

        String columns = "tp.c_id id\n" +
                "\t,tp.c_user_id objId\n" +
                "\t,tp.c_name name\n" +
                "\t,te.c_name extentName\n" +
                "\t,tp.c_type_name typeName\n" +
                "\t,tcsp.c_online_type onlineType\n" +
                "\t,c_onguard_type onguardType\n" +
                "\t,tcsp.c_is_in_extent isInExtent\n" +
                "\t,tcsp.c_is_in_work_time isInWorkTime\n" +
                "\t,tsu.c_image_url imageUrl\n" +
                "\t,(\n" +
                "\t\tSELECT sum(TIMESTAMPDIFF(SECOND, CONCAT (\n" +
                "\t\t\t\t\t\tcast(now() AS DATE)\n" +
                "\t\t\t\t\t\t,' '\n" +
                "\t\t\t\t\t\t,tddt.c_begin\n" +
                "\t\t\t\t\t\t), CONCAT (\n" +
                "\t\t\t\t\t\tcast(now() AS DATE)\n" +
                "\t\t\t\t\t\t,' '\n" +
                "\t\t\t\t\t\t,tddt.c_end\n" +
                "\t\t\t\t\t\t)))\n" +
                "\t\tFROM t_dingtalk_duty_times tddt\n" +
                "\t\tJOIN t_dingtalk_duty tdd ON tdd.c_data_id = tddt.c_duty_data_id\n" +
                "\t\tWHERE tdd.c_id = tsr.c_shift_id\n" +
                "\t\t) totalTime\n" +
                "\t,tcsp.c_online_time onlineTime\n" +
                "\t,tcsp.c_valid_time validTime\n" +
                "\t,tcsp.c_mileage mileage\n" +
                "\t,tcsp.c_geo_x lng\n" +
                "\t,tcsp.c_geo_y lat\n" +
                "\t,tcsp.c_gps_date gpsDate\n"+
                "\t,ifnull((select count(1) from t_give_an_alarm ga where ga.c_obj_type=0  and ga.c_obj_id_int =  tp.c_id and c_begin_time BETWEEN  CAST(now() AS DATE) and now() group by c_obj_id_int),0) c_alarm_count\n" +
                "\t,ifnull((select count(1) from t_clock_in_people cp where cp.c_db_status=0 and  cp.c_user_id_int =  tp.c_id and cp.c_date = CAST(now() AS DATE)     group by cp.c_user_id_int),0) c_clock_count\n"+
                "\t,ifnull((select count(1) from t_clock_in_people cp2 where  cp2.c_db_status=0 and cp2.c_user_id_int =  tp.c_id and cp2.c_date = CAST(now() AS DATE) and  cp2.c_status>0 group by cp2.c_user_id_int),0) c_clock_in_count\n";

        SqlQueryMate sqlQueryMate = new SqlQueryMate(table).setColumns(columns);
        sqlQueryMate
                .where(true,"tsr.c_obj_type",ScriptConditionEnum.Eq,1)
                .where(query.getExtentId()!=null&&query.getExtentId()>0, "tsr.c_extent_id", ScriptConditionEnum.Eq, query.getExtentId())
                .where(query.getIsInExtent()!=null, "tcsp.c_is_in_extent", ScriptConditionEnum.Eq, query.getIsInExtent())
                .where(query.getIsInWorkTime()!=null, "tcsp.c_is_in_work_time", ScriptConditionEnum.Eq, query.getIsInWorkTime())
        ;

        return getMap(sqlQueryMate);
    }

    @Override
    public Map getExtentWorkPersonAndCarInfo(ScheduleRecordQuery query) {
        String table = "dual";

        String columns =
                "(select count(1) from t_person tp join t_device_object_relate tdor on tp.c_id=tdor.c_object_id and c_object_type=0 and tdor.c_db_status=0 and tp.c_db_status=0) peopleCount,\n" +
                        "(select count(1) from t_car tc join t_device_object_relate tdor on tc.c_id=tdor.c_object_id and c_object_type=1 and tdor.c_db_status=0 and tc.c_db_status=0) carCount";

        SqlQueryMate sqlQueryMate = new SqlQueryMate(table).setColumns(columns);
        sqlQueryMate
                .where(query.getExtentId()!=null &&query.getExtentId()>0, "tsr.c_extent_id", ScriptConditionEnum.Eq, query.getExtentId())
        ;
        return getOneMap(sqlQueryMate);
    }

    @Override
    public List<Map<String, Object>> getPersonShiftWorkInfo(ScheduleRecordQuery query) {
        String table = "t_schedule_record tsr \n" +
                "join t_person tp on tp.c_id=tsr.c_obj_id\n" +
                "join t_dingtalk_duty tdd on tsr.c_shift_id=tdd.c_id\n" +
                "join t_dingtalk_duty_times tddt on tdd.c_data_id=tddt.c_duty_data_id and tsr.c_date=cast(now() as date)";

        String columns = "tdd.c_name name,tddt.c_begin begin,tddt.c_end end,0 workTime,0 workMileage";

        SqlQueryMate sqlQueryMate = new SqlQueryMate(table).setColumns(columns);
        sqlQueryMate
                .where(true,"tsr.c_obj_type",ScriptConditionEnum.Eq,1)
                .where(true,"tsr.c_obj_id",ScriptConditionEnum.Eq,query.getObjectId())
        ;
        return getMap(sqlQueryMate);
    }

    @Override
    public List<Map<String, Object>> getCarShiftWorkInfo(ScheduleRecordQuery query) {
        String table = "t_schedule_record tsr \n" +
                "join t_car tc on tc.c_id=tsr.c_obj_id\n" +
                "join t_dingtalk_duty tdd on tsr.c_shift_id=tdd.c_id\n" +
                "join t_dingtalk_duty_times tddt on tdd.c_data_id=tddt.c_duty_data_id and tsr.c_date=cast(now() as date)";

        String columns = "tdd.c_name name,tddt.c_begin begin,tddt.c_end end,0 workTime,0 workMileage";

        SqlQueryMate sqlQueryMate = new SqlQueryMate(table).setColumns(columns);
        sqlQueryMate
                .where(true,"tsr.c_obj_type",ScriptConditionEnum.Eq,2)
                .where(true,"tsr.c_obj_id",ScriptConditionEnum.Eq,query.getObjectId())
        ;
        return getMap(sqlQueryMate);
    }

    @Override
    public List<Map<String, Object>> getTaskInfo(ScheduleRecordQuery query) {
        String table = "t_schedule_record tsr \n" +
                "join t_task_facilities_relation ttfr on tsr.c_task_id=ttfr.c_task_id and ttfr.c_db_status=0\n" +
                "join t_facilities tf on ttfr.c_facilities_id=tf.c_id and tsr.c_date=cast(now() as date)";

        String columns = " tf.c_name name,0 status";

        SqlQueryMate sqlQueryMate = new SqlQueryMate(table).setColumns(columns);
        sqlQueryMate
                .where(true,"tsr.c_obj_type",ScriptConditionEnum.Eq,query.getObjectTypeId())
                .where(true,"tsr.c_obj_id",ScriptConditionEnum.Eq,query.getObjectId())
        ;
        return getMap(sqlQueryMate);
    }

    @Override
    public void insertStatusPerson() {
        String sql="insert into t_curr_status_people(c_user_id,c_user_name,c_number,c_gps_id,c_client_key,c_section_id,c_section_name,c_type,c_person_type,\n" +
                "c_online_type,c_onguard_type,c_online_time,c_valid_time,c_battery,c_mileage,c_gps_state,c_is_in_section,c_is_section_area,c_onguard_time,c_valid_mileage,c_onguard_state,c_warning_ext_info_child_0\n" +
                ",c_db_status,c_db_create_date,c_db_create_id,c_db_last_update_date,c_db_last_update_id,c_is_in_extent,c_is_in_work_time)\n" +
                "select tp.c_user_id,tp.c_name,tsu.c_user_idcard,tdor.c_device_id,tdor.c_code,ts.c_section_id,tp.c_section_name,tp.c_type,tp.c_type_name,\n" +
                "0,0,0,0,0,0,0,0,0,0,0,0,0,\n" +
                "0,now(),0,now(),0,0,0\n" +
                "from t_schedule_record tsr\n" +
                "join t_person tp on tsr.c_obj_id=tp.c_id and tsr.c_obj_type=1\n" +
                "join t_sys_user tsu on tp.c_user_id=tsu.c_user_id\n" +
                "join t_section ts on tp.c_section_id=ts.c_id\n" +
                "left join t_device_object_relate tdor on tdor.c_object_id=tp.c_id and tdor.c_object_type=0\n" +
                "where not exists(\n" +
                "\tselect 1 from t_curr_status_people tcsp \n" +
                "\twhere tcsp.c_user_id=tp.c_user_id\n" +
                ") and c_date=cast(now() as date)";
        run(sql,CommandType.Text,dbCommand -> {
            dbCommand.executeNonQuery();
        });
    }

    @Override
    public void insertStatusCar() {
        String sql="insert into t_curr_status_car(c_car_id,c_number,c_gps_id,c_client_key,c_section_id,c_section_name,c_type,c_car_type\n" +
                ",c_online_type,c_onguard_type,c_work_type,c_online_time,c_valid_time,c_mileage,c_speed,c_acc_state,c_is_in_section,c_is_section_area,c_onguard_time,c_is_act_10m,c_valid_mileage,c_onguard_state,c_work_area_id,c_work_mileage,c_work_is_valid,c_is_work_line,c_work_line_id,c_car_work_status,c_in_section_use_water,c_out_section_use_water,c_total_water\n" +
                ",c_db_status,c_db_create_date,c_db_create_id,c_db_last_update_date,c_db_last_update_id,c_is_in_extent,c_is_in_work_time)\n" +
                "select tc.c_car_id,tc.c_car_num,tdor.c_device_id,tdor.c_code,ts.c_section_id,tc.c_section_name,tc.c_type,tc.c_type_name,\n" +
                "0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,\n" +
                "0,now(),0,now(),0,0,0\n" +
                "from t_schedule_record tsr\n" +
                "join t_car tc on tsr.c_obj_id=tc.c_id and tsr.c_obj_type=2\n" +
                "join t_section ts on tc.c_section_id=ts.c_id\n" +
                "left join t_device_object_relate tdor on tdor.c_object_id=tc.c_id and tdor.c_object_type=1\n" +
                "where not exists(\n" +
                "\tselect 1 from t_curr_status_car tcsc \n" +
                "\twhere tcsc.c_car_id=tc.c_car_id\n" +
                ") and c_date=cast(now() as date)";
        run(sql,CommandType.Text,dbCommand -> {
            dbCommand.executeNonQuery();
        });
    }
}
